package Region;

import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class DatabaseManager {
    private Connection connection;
    private static final String MYSQL_JDBC_URL = "jdbc:mysql://localhost:3306/?user=root&password=wangzikang2002";

    public DatabaseManager(){
        try {
            // 步骤 1：连接到 MySQL 实例
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(MYSQL_JDBC_URL);
            if (connection != null) {

                // 检查数据库是否存在并删除
                dropDatabaseIfExists();
                // 创建新数据库
                createNewDatabase();
                connection.close();
                // 连接到刚创建的数据库
                connectToSpecificDatabase();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    private void dropDatabaseIfExists() throws SQLException {
        Statement statement = connection.createStatement();
        String sql = "DROP DATABASE IF EXISTS localRegion";
        statement.execute(sql);
    }

    private void createNewDatabase() throws SQLException {
        Statement statement = connection.createStatement();
        String sql = "CREATE DATABASE localRegion";
        statement.execute(sql);

        String sql3 = "use localRegion";
        statement.execute(sql3);
    }

    private void connectToSpecificDatabase() throws SQLException {
        String specificDbUrl = "jdbc:mysql://localhost:3306/localRegion?user=root&password=wangzikang2002";
        connection = DriverManager.getConnection(specificDbUrl);
        if (connection != null) {
            System.out.println("MySQL连接成功");
        }
    }

    //MySQL运行传入的SQL语句
    public String updateSQL(String sql) throws SQLException {
        try {
            System.out.println(sql);
            Statement statement = connection.createStatement();
            int result = statement.executeUpdate(sql);
            System.out.println("表更新成功");
            return result+"";
        }catch (Exception e){
            System.out.println(e);
            return e.toString();
        }
    }


    public String selectSQL(String sql) throws SQLException {
        StringBuilder result = new StringBuilder();
        ArrayList<Integer> columnMaxWidths = new ArrayList<>();
        ArrayList<String> columnNames = new ArrayList<>();
        try {
            Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet resultSet = statement.executeQuery(sql);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            // Initialize column widths and names
            for (int i = 1; i <= columnCount; i++) {
                columnNames.add(metaData.getColumnName(i));
                columnMaxWidths.add(Math.max(metaData.getColumnName(i).length(), metaData.getColumnDisplaySize(i)));
            }

            // Fetch records to determine max column widths
            while (resultSet.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String value = resultSet.getString(i);
                    if (value != null) {
                        columnMaxWidths.set(i - 1, Math.max(columnMaxWidths.get(i - 1), value.length()));
                    }
                }
            }

            resultSet.beforeFirst(); // Move cursor before the first row

            // Construct the top border of the table
            StringBuilder topBorder = new StringBuilder();
            for (Integer width : columnMaxWidths) {
                topBorder.append("+");
                for (int i = 0; i < width; i++) {
                    topBorder.append("-");
                }
            }
            topBorder.append("+\n");
            result.append(topBorder);

            // Construct the header row
            StringBuilder headerRow = new StringBuilder();
            headerRow.append("|");
            for (int i = 0; i < columnCount; i++) {
                String format = "%-" + columnMaxWidths.get(i) + "s|";
                headerRow.append(String.format(format, columnNames.get(i)));
            }
            headerRow.append("\n");
            result.append(headerRow);

            // Add the border after header
            result.append(topBorder);

            // Construct the data rows
            while (resultSet.next()) {
                StringBuilder dataRow = new StringBuilder();
                dataRow.append("|");
                for (int i = 1; i <= columnCount; i++) {
                    String value = resultSet.getString(i);
                    if (value == null) { value = "null"; }
                    String format = "%-" + columnMaxWidths.get(i - 1) + "s|";
                    dataRow.append(String.format(format, value));
                }
                dataRow.append("\n");
                result.append(dataRow);
            }

            // Add the bottom border
            result.append(topBorder);

        } catch (Exception e) {
            System.out.println(e);
        }
        System.out.println("表查询成功");
        return result.toString();
    }
}
